package br.com.statizo.financeiro.core.repository.impl;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import snoghert.db.util.PersistenceUtil;
import br.com.statizo.financeiro.domain.entity.impl.Lancamento;
import br.com.statizo.financeiro.domain.enums.GrupoLancamento;
import br.com.statizo.financeiro.domain.repo.impl.RepositorioAbstrato;

public class LancamentoRepository extends RepositorioAbstrato<Lancamento>{

	public LancamentoRepository(PersistenceUtil persistenceUtility) {
		super(persistenceUtility);
	}

	public void setPago(Serializable id,boolean pago){
		Query query = entityManager.createQuery("update Lancamento set pago = :pago, agendado = :agendado where id = :id");
		query.setParameter("agendado",false).setParameter("pago", pago).setParameter("id", id);
		query.executeUpdate();
	}

	public void setAgendado(Serializable id,boolean agendado){
		Query query = entityManager.createQuery("update Lancamento set agendado = :agendado, pago = :pago where id = :id");
		query.setParameter("agendado", agendado).setParameter("pago",false).setParameter("id", id);
		query.executeUpdate();
	}

	public void liberarSaldo(int mes, int ano){
		Query query = entityManager.createQuery("delete from Lancamento where saldo = :saldo and month(dataDivida) = :mes and year(dataDivida) = :ano");
		query.setParameter("saldo", true);
		query.setParameter("mes", mes + 1);
		query.setParameter("ano", ano);
		query.executeUpdate();
	}
	
	public boolean isSaldoExistente(int mes, int ano){
		Query query = entityManager.createQuery("select count(id) from Lancamento where saldo= :saldo and month(dataDivida) = :mes and year(dataDivida) = :ano", Long.class);
		query.setParameter("saldo", true);
		query.setParameter("mes", mes + 1);
		query.setParameter("ano", ano);
		return ((Long)query.getSingleResult()) > 0;
		
	}

	public double getReceitaTotal(int mes, int ano, Serializable contaId){

		StringBuilder queryStr = new StringBuilder("select sum(valor) from Lancamento where saldo = :saldo and month(dataPagamento) = :mes and year(dataPagamento) = :ano ");
		queryStr.append("and categoria.grupo = :receita");
		
		if(contaId != null){
			queryStr.append(" and conta.id = :contaId");
		}

		Query query = entityManager.createQuery(queryStr.toString(), Double.class);
		query.setParameter("saldo", false);
		query.setParameter("mes", mes + 1);
		query.setParameter("ano", ano);
		query.setParameter("receita", GrupoLancamento.RECEITA);
		
		if(contaId != null){
			query.setParameter("contaId", contaId);	
		}
		
		Object result = query.getSingleResult();
		
		if(result != null)
			return ((Double) result);
		return 0.0d;
	}
	
	
	public double getDespesaTotal(int mes, int ano, Serializable contaId){

		StringBuilder queryStr = new StringBuilder("select sum(valor) from Lancamento where saldo = :saldo and month(dataPagamento) = :mes and year(dataPagamento) = :ano ");
		queryStr.append("and categoria.grupo != :receita");

		if(contaId != null){
			queryStr.append(" and conta.id = :contaId");
		}
		
		Query query = entityManager.createQuery(queryStr.toString(), Double.class);
		query.setParameter("saldo", false);
		query.setParameter("mes", mes + 1);
		query.setParameter("ano", ano);
		query.setParameter("receita", GrupoLancamento.RECEITA);
		
		if(contaId != null){
			query.setParameter("contaId", contaId);	
		}
		
		Object result = query.getSingleResult();

		if(result != null)
			return ((Double) result);
		return 0.0d;
		
	}
	
	
	
	public List<Lancamento> getListBy(int mes, int ano, Serializable contaId){

		CriteriaBuilder cb = entityManager.getCriteriaBuilder();
		CriteriaQuery<Lancamento> cq =  cb.createQuery(Lancamento.class);
		Root<Lancamento> root = cq.from(Lancamento.class);
		CriteriaQuery<Lancamento> select = cq.select(root);

		Path<Calendar> dtPg = root.get("dataPagamento");
		Path<Calendar> dtDiv = root.get("dataDivida");
		Path<Boolean> saldo = root.get("saldo");
		

		List<Predicate> predicates = new ArrayList<Predicate>();
		
		Predicate predicate = cb.and(
				cb.or(
						cb.and(
								cb.equal(cb.function("year", Integer.class, dtPg), ano),
								cb.equal(cb.function("month", Integer.class, dtPg), mes)
								),
						cb.and(
								cb.isTrue(saldo),
								cb.equal(cb.function("year", Integer.class, dtDiv), ano),
								cb.equal(cb.function("month", Integer.class, dtDiv), mes)
								)
						)
	    );
		
		predicates.add(predicate);
		
		
		if(contaId != null){
			Path<Serializable> conta = root.get("conta").get("id");
			predicates.add(
					cb.and(cb.equal(conta, contaId))
					);
		}
		
		select.orderBy(cb.asc(dtPg),cb.asc(dtDiv));
		cq.where(predicates.toArray(new Predicate[0]));

		return entityManager.createQuery(cq).getResultList();
	}

	public void deleteChilds(Serializable id){
		Query query = entityManager.createQuery("delete from Lancamento where primeiraParcela.id = :id");
		query.setParameter("id", id);
		query.executeUpdate();
	}
	
	public double obterMontante(int mes, int ano, GrupoLancamento grupo){
		String jpql ="select sum(valor) from Lancamento where categoria.grupo = :grupo and month(dataPagamento) = :month and year(dataPagamento) = :year";
		Query query = entityManager.createQuery(jpql,Double.class)
				.setParameter("grupo", grupo)
				.setParameter("month", mes)
				.setParameter("year", ano);
		return (double) query.getSingleResult();
	}

}
